import warnings
warnings.filterwarnings("ignore")
import numpy as np
import time
from pandas import read_excel
from pandas import DataFrame
from sklearn.preprocessing import LabelEncoder, OneHotEncoder
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split
from xgboost import XGBRegressor
from sklearn.metrics import accuracy_score
import matplotlib.pyplot as plt
from xgboost import plot_importance
# https://www.cnblogs.com/gdjlc/p/11409804.html

df = read_excel(r'C:\Users\zll\Desktop\JMS_\RawData.xlsx',Sheetname='Sheet1',header=0 )
len1 = df['Case ID'].__len__()
continuous_conds = df.ix[:,['StartTime1',  'EndTime1', 'Cost1',
             'StartTime2',  'EndTime2', 'Cost2',
             'StartTime3',  'EndTime3', 'Cost3',
             'StartTime4',  'EndTime4', 'Cost4',
             'StartTime5',  'EndTime5', 'Cost5',
             'StartTime6',  'EndTime6', 'Cost6',
             'StartTime7',  'EndTime7', 'Cost7',
             'StartTime8',  'EndTime8', 'Cost8',
             'StartTime9',  'EndTime9', 'Cost9'
            ]].values

endtime11  = df['EndTime11'].values
endtime10 = df['EndTime10'].values
endtime = np.zeros((len1))

total_time = []

continuous_res = np.zeros([len1, 27])
for i in range(len1):
    for j in range(27):
        if type(continuous_conds[i, j]) == str:
                continuous_res[i, j] = int(continuous_conds[i, j].split(":")[0]) * 3600 + int(continuous_conds[i, j].split(":")[1]) * 60 + int(continuous_conds[i, j].split(":")[2])
        elif type(continuous_conds[i, j]) == int or type(continuous_conds[i, j]) == float:
            continuous_res[i, j] = continuous_conds[i, j]
        else:
            continuous_res[i, j] = continuous_conds[i, j].hour *  3600 + continuous_conds[i, j].minute * 60 + continuous_conds[i, j].second
    if type(endtime11[i]) == str:
        endtime[i] = int(endtime11[i].split(":")[0]) * 3600 + int(endtime11[i].split(":")[1]) * 60 + int(endtime11[i].split(":")[2])
    elif type(endtime10[i]) == str:
        endtime[i] = int(endtime10[i].split(":")[0]) * 3600 + int(endtime10[i].split(":")[1]) * 60 + int(endtime10[i].split(":")[2])
    elif endtime10[i] == 0:
        endtime[i] = endtime11[i].hour * 3600 + endtime11[i].minute * 60 + endtime11[i].second
    else:
        endtime[i] = endtime10[i].hour * 3600 + endtime10[i].minute * 60 + endtime10[i].second
    total_time.append(endtime[i] - continuous_res[i, 0])
total_time = np.array(total_time)

dispersed_conds = df.ix[:,[
                           'Register_Request',  'Reason1', 'Resource1',   #1, 3, 1   5
                           'Date_Check', 'Type2', 'Resource2',            #1, 2, 1   9
                           'Mode_Audit', 'Resource3',   #1,  1   11
                           'Manual_Review',  'Resource4', #2,  2   15
                           'Reason_Review', 'Result5', 'Resource5',  #2, 3, 2   22
                           'Ticket_Check', 'Valid6', 'Resource6',    # 2, 3, 2   29
                           'Casually_Examine', 'Result7', 'Resource7', # 2, 3, 2   36
                           'Thoroughly_Examine', 'Result8', 'Resource8', # 2, 3, 2   43
                           'Decide',  'Resource9'  # 2,  2   47
             ]].values

le = LabelEncoder()
oh = OneHotEncoder(sparse=False)
for i in range(len(dispersed_conds[0])):
    dispersed_conds[:, i] = le.fit_transform(dispersed_conds[:, i])
oh.fit(dispersed_conds)
onehot_enc = oh.transform(dispersed_conds)
onehot_enc = oh.transform(dispersed_conds)

#将同一index归并
arr1 = [5, 9, 11, 15, 22, 29, 36, 43, 47]
to_traineAttrs = onehot_enc[:, :5]
for i in range(1, len(arr1)):
    to_traineAttrs = np.hstack((to_traineAttrs, continuous_res[:, 3*(i-1): 3*i], onehot_enc[:, arr1[i-1]:arr1[i]]))
# to_traineAttrs = np.hstack((to_traineAttrs, continuous_res[:, 21:24], onehot_enc[:, 48:]))
res = np.hstack((to_traineAttrs, continuous_res[:, 24:]))

# [5, 9, 11, 15,  22,  29, 36, 43, 47]
#  A， B, C,  D,   E,  H,  F,  G,  I
# [8, 15, 20, 27, 37, 47, 57, 67, 74]
#A
tableA = res[:, :8].copy()
remaining_timeA = []
for i in range(len1):
    remaining_timeA.append(endtime[i] - tableA[i, -2])
remaining_timeA = np.array(remaining_timeA)
#B
tableB = res[:, :15].copy()
remaining_timeB = []
for i in range(len1):
    remaining_timeB.append(endtime[i] - tableB[i, -2])
remaining_timeB = np.array(remaining_timeB)
#C
tableC = res[:, :20].copy()
remaining_timeC = []
for i in range(len1):
    remaining_timeC.append(endtime[i] - tableC[i, -2])
remaining_timeC = np.array(remaining_timeC)
# spilt table
res1 = []
res2 = []
endtime1 = []
endtime2 = []
for i in range(len(res)):
    if continuous_res[i, 9] == 0:
        res1.append(res[i])
        endtime1.append(endtime[i])
    else:
        res2.append(res[i])
        endtime2.append(endtime[i])
res1 = np.array(res1)
res1 = np.hstack((res1[:, :20].copy(), res1[:, 27:74].copy()))
endtime1 = np.array(endtime1)
res2 = np.array(res2)
res2 = res2[:, :27].copy()
endtime2 = np.array(endtime2)
#D
tableD = res2.copy() #manual review
remaining_timeD = []
for i in range(len(res2)):
    remaining_timeD.append(endtime2[i] - tableD[i, -2])
remaining_timeD = np.array(remaining_timeD)
#E
tableE = res1[:, :30].copy() #Reason_Review
remaining_timeE = []
for i in range(len(res1)):
    remaining_timeE.append(endtime1[i] - tableE[i, -2])
remaining_timeE = np.array(remaining_timeE)

# [5, 9, 11, 15,  22,  29, 36, 43, 47]
#  A，    B,    C,     D,     E,     H,      F,    G,     I
# [0-8, 8-15, 15-20, 20-27, 27-37, 37-47, 47-57, 57-67, 67-74]
#H
tableH = res1[:, :60].copy()
for i in range(len(tableH)):
    if tableH[i, 47] + tableH[i, 57] > tableH[i, 37]:
        for j in range(40, 60):
            tableH[i, j] = 0
remaining_timeH = []
for i in range(len(res1)):
    remaining_timeH.append(endtime1[i] - tableH[i, 38])
remaining_timeH = np.array(remaining_timeH)

resF = []
endtimeF = []
resG = []
endtimeG = []
for i in range(len(res1)):
    if res1[i, 47] == 0:
        resG.append(res1[i])
        endtimeG.append(endtime1[i] - res1[i, 57])
    else:
        resF.append(res1[i])
        endtimeF.append(endtime1[i] - res1[i, 47])
resF = np.array(resF)
resG = np.array(resG)
remaining_timeF = np.array(endtimeF)
remaining_timeG = np.array(endtimeG)

#  A，    B,    C,       E,     H,     F,    G,     I
# [0-8, 8-15, 15-20,  20-30, 30-40, 40-50, 50-60, 60-67]
tableF = resF[:, :60].copy()
tableG = resG[:, :60].copy()
for i in range(len(tableF)):
    if tableF[i, 47] + tableF[i, 57] < tableF[i, 37]:
        for j in range(30, 40):
            tableF[i, j] = 0

for i in range(len(tableG)):
    if tableG[i, 47] + tableG[i, 57] < tableG[i, 37]:
        for j in range(30, 40):
            tableG[i, j] = 0
#I
tableI = res1.copy()
remaining_timeI = []
for i in range(len(res1)):
    remaining_timeI.append(endtime1[i] - tableI[i, -2])
remaining_timeI = np.array(remaining_timeI)
# xgboost
# https://www.cnblogs.com/wanglei5205/p/8578486.html
# arr1 =    [5,  9,  11,  15,  22,  29,  36,  43,  47]
imp_split = [ 8,  15,  20,  27,  37,  47,  57,  67,  74]
tableList = [tableA, tableB, tableC, tableD, tableE, tableF, tableG, tableH, tableI]
labelList = [remaining_timeA, remaining_timeB, remaining_timeC, remaining_timeD, remaining_timeE, remaining_timeF, remaining_timeG, remaining_timeH, remaining_timeI]
for k in range(len(tableList)):
    table = tableList[k]
    label = labelList[k]
    X_train, X_test, y_train, y_test = train_test_split( table , label, random_state=0, test_size=.2 )
    model = XGBRegressor()
    # model = RandomForestClassifier(random_state=0)
    model.fit(X_train, y_train)
    y_pred = model.predict(X_test)
    mae = np.sum(np.abs(y_pred - y_test)) / (len(y_test) * 60.0)
    print("mae is : " + str(mae))

print("done")